package dfbz.com.dao;


import dfbz.com.constant.Constance;
import dfbz.com.dao.base.BaseDao;
import dfbz.com.model.User;
import dfbz.com.model.UserFocus;
import dfbz.com.model.UserInfo;
import dfbz.com.utils.JDBCUtil;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class UserDao extends BaseDao {

    /**
     * 用户登录
     * 如果找到这个用户名字，则封装并返回这个用户对象
     * 一张表对应一个model（类）——DbUtil方便对象的封装 QueryRunner
     * 辅助类并没有表对应
     *
     * @param username 用户名
     * @return 用户对象
     */

    QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
    //加入转下划线为驼峰的对象——将数据库中字段对应model中对象的字段
    BasicRowProcessor bp = new BasicRowProcessor(new GenerousBeanProcessor());

    public User findUserByName(String username) {
        try {
            User user = qr.query("select * from user where username = ? limit 1", new BeanHandler<>(User.class, bp), username);
            System.out.println("找到了用户：" + user);
            return user;
        } catch (SQLException e) {
            e.printStackTrace();
            e.getMessage();
        }
        return null;

    }


    public User findUserByEmail(String email) {
        try {
            User user = qr.query("select * from user where email = ? limit 1", new BeanHandler<>(User.class, bp), email);
            return user;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }


    /**
     * 查取总用户数量
     *
     * @return
     */
    public Integer findTotalCount() {

        String sql = "select count(*) from user";
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                int count = resultSet.getInt(1);
                System.out.println("查到的总数：" + count);
                return count;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            JDBCUtil.close(resultSet, preparedStatement, connection);
        }

        return null;
    }


    /**
     * 联表查询，得到用户的详细信息
     *
     * @param userId
     * @return UserDetail
     */

    public Map<String, Object> findUserDetail(String userId) {
        Map<String, Object> query = null;
        try {
            String sql = "select c.*,d.name as dept_name from \n" +
                    "(select u.username,u.email ,u.is_secret,u.dept_id,i.*\n" +
                    "from user u\n" +
                    "left join user_info i\n" +
                    "on u.id=i.user_id\n" +
                    "where u.id = ?) c\n" +
                    "left join dept d\n" +
                    "on c.dept_id = d.id";
            query = qr.query(sql, new MapHandler(), Integer.parseInt(userId));
//            System.out.println("找到了用户详情：" + query);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return query;

    }


    //用重载的方法也可以；重载一：
    public List<UserFocus> findUserFocus(Object userId) {

        String sql = "select *\n" +
                "from user_focus  where user_id = ?";

        try {
            List<UserFocus> query = qr.query(sql, new BeanListHandler<>(UserFocus.class, bp), userId);
            return query;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    // 重载二：
    public List<UserFocus> findUserFocus(Object userId, Integer current) {

        String sql = "select *\n" +
                "from user_focus  where user_id = ? limit ?,?";
        try {
            List<UserFocus> query = qr.query(sql, new BeanListHandler<>(UserFocus.class, bp), userId, (current - 1) * Constance.PAGE_ROW_COUNT, Constance.PAGE_ROW_COUNT);
            return query;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<Map<String, Object>> findPageUser(Integer userId, Integer current, String search) {
        List<Map<String, Object>> query = null;
        StringBuilder sb = new StringBuilder();
        sb.append("select u.id,u.username,f.age,f.real_name,f.phone,f.gender,f.describe,fo.user_focus_id\n" +
                "from user u\n" +
                "left join user_info f\n" +
                "on u.id = f.user_id\n" +
                "left join user_focus fo\n" +
                "on u.id = fo.user_focus_id and fo.user_id = ? ");
        if (null != search) {
            try {
                sb.append("where u.username like ? or f.real_name like ?");
                sb.append("order by u.id limit ?,?");
                query = qr.query(sb.toString(), new MapListHandler(), userId, search + "%", search + "%", (current - 1) * Constance.PAGE_ROW_COUNT, Constance.PAGE_ROW_COUNT);

            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else {
            try {
                sb.append("order by u.id limit ?,?");
                query = qr.query(sb.toString(), new MapListHandler(), userId, (current - 1) * Constance.PAGE_ROW_COUNT, Constance.PAGE_ROW_COUNT);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return query;

    }

    public long findListTotalCount(String search) {
        String sql;
        Map<String, Object> query;
        if (search == null) {
            try {
                sql = "select count(*) as total from user ";
                query = qr.query(sql, new MapHandler());
                return (Long) query.get("total");
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else {
            try {
                sql = "select count(*) as total from user u left join user_info f  on u.id = f.user_id \n" +
                        "where u.username like ? or f.real_name like ?";
                query = qr.query(sql, new MapHandler(), search + "%", search + "%");
                return (Long) query.get("total");
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }


    public UserFocus findFollowData(String userId, String uId) {

        String sql = "select * from user_focus where user_id=? and user_focus_id=?";

        UserFocus query = null;
        try {
            query = qr.query(sql, new BeanHandler<>(UserFocus.class, bp), Integer.parseInt(userId), Integer.parseInt(uId));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }


    public void addFocus(UserFocus userFocus) {

        add(userFocus);
    }

    public Long findFocusTotalCount(Integer total) {
        String sql = "select count(*) as count from user_focus where user_id = ?";
        Map<String, Object> query = null;
        try {
            query = qr.query(sql, new MapHandler(), total);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return (Long) query.get("count");

    }

    public void deleteFollow(String userId, String uId) {

        String sql = "delete from user_focus where user_id = ? and user_focus_id = ?";
        try {
            qr.execute(sql, Integer.parseInt(userId), Integer.parseInt(uId));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void updateImg(String path, String id) {

        String sql = "update user_info set pic = ? where user_id = ?";
        try {
            qr.execute(sql, path, Integer.parseInt(id));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public UserInfo findUserInfo(Integer id) {

        String sql = "select * from user_info where user_id=?";

        UserInfo query = null;
        try {
            query = qr.query(sql, new BeanHandler<>(UserInfo.class, bp), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }


    public Integer findDeptIdByDeptName(String dept) {

        String sql = "select id from dept where name = ?";
        Map<String, Object> query = null;
        try {
            query = qr.query(sql, new MapHandler(), dept);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return (Integer) query.get("id");
    }

    public void addLook(String uId) {

        String sql = " update user_info set look = look+1 where user_id = ? ";
        try {
            qr.execute(sql,Integer.parseInt(uId));
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public Integer findFocusCount(String uId) {

        String sql = "select count(*) as count from user_focus where user_focus_id = ?";
        Map<String, Object> query = null;
        try {
            query = qr.query(sql, new MapHandler(), Integer.parseInt(uId));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return Integer.parseInt(query.get("count").toString());
    }

    public Integer findMyFocusCount(String userId) {

        String sql = "select count(*) as count from user_focus where user_id = ?";
        Map<String, Object> query = null;
        try {
            query = qr.query(sql, new MapHandler(), Integer.parseInt(userId));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return Integer.parseInt(query.get("count").toString());
    }
}
